"""
Case Type   : 基础算子性能提升-快速参数化增加视图查询功能
Case Name   : query长度小于等于512才缓存
Create At   : 2025-10-22
Owner       : sungang14
Description :
    1、set enable_query_parameterization = on;set max_parameterized_query_stored = 512;
    2、执行create table语句
    3、执行长度512的parameterized_query,查看query_parameterization_views
    4、执行长度513的parameterized_query,查看query_parameterization_views
Expect      :
    1、设置成功
    2、成功
    3、有一条记录
    4、没有记录
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.show_query_parameterization = 'show enable_query_parameterization;'
        self.set_query_parameterization = "enable_query_parameterization = ON"
        self.set_query_parameterization_01 = "enable_query_parameterization = OFF"
        self.table_name = f"query_parameterization_views_case0020"
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"""create table {self.table_name}(
                                        cc_call_center_sk integer not null,
                                        cc_call_center_id char(16) not null,
                                        cc_rec_start_date date ,
                                        cc_rec_end_date date ,
                                        cc_closed_date_sk integer ,
                                        cc_open_date_sk integer ,
                                        cc_name varchar(50) ,
                                        cc_class varchar(50) ,
                                        cc_employees integer ,
                                        cc_sq_ft integer ,
                                        cc_hours char(20) ,
                                        cc_manager varchar(40) ,
                                        cc_mkt_id integer ,
                                        cc_mkt_class char(50) ,
                                        cc_mkt_desc varchar(100) ,
                                        cc_market_manager varchar(100) ,
                                        cc_division varchar(100) ,
                                        cc_division_name varchar(50) ,
                                        cc_company varchar(100) ,
                                        cc_company_name char(50) ,
                                        cc_street_number char(10) ,
                                        cc_street_name varchar(60) ,
                                        cc_street_type char(15) ,
                                        cc_suite_number char(10) ,
                                        cc_city varchar(60) ,
                                        cc_county varchar(30) ,
                                        cc_state char(50) ,
                                        cc_zip char(20) ,
                                        cc_country varchar(20) ,
                                        cc_gmt_offset decimal(5,2) ,
                                        cc_tax_percentage decimal(5,2)
                                    );"""
        self.prepare_data_02 = f"insert into {self.table_name} values(11,'askjhfgadaslf','2020-01-01 00:00:00','2020-01-01 00:00:00',11,11,'dakjhsfgALSasf','ASFHakjsfghlakhsfga',11,11,'skdjfhasdfgas','akjdsfhalskdjghasgwaeaerg',11,'sdhsethwsdgawrgawergadgasg','100ASKFvgakjshfvkAGEFVKAshjf','100ASKFvgakjshfvkAVKAshjf','100ASKFvgakjshfvkAVKAshjf','AFDSFASDFAFSADF','100ASKFAGEFVshjf','sdfsfsdfs','sjfghL','60ASKFvsGEFVKAsf','askffadf','dsfhsfh','sdfjhakidf','sadkljdhlaa','sdgadgagasg','dsfhghjh','asdkjfgal',123.33,212.123);" \
                               f"select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"
        self.prepare_data_03 = f"insert into {self.table_name} values(11,'askjhfgadaslfa','2020-01-01 00:00:00','2020-01-01 00:00:00',11,11,'dakjhsfgALSasf','ASFHakjsfghlakhsfga',11,11,'skdjfhasdfgas','akjdsfhalskdjghasgwaeaerg',11,'sdhsethwsdgawrgawergadgasg','100ASKFvgakjshfvkAGEFVKAshjf','100ASKFvgakjshfvkAVKAshjf','100ASKFvgakjshfvkAVKAshjf','AFDSFASDFAFSADF','100ASKFAGEFVshjf','sdfsfsdfs','sjfghL','60ASKFvsGEFVKAsf','askffadf','dsfhsfh','sdfjhakidf','sadkljdhlaa','sdgadgagasg','dsfhghjh','asdkjfgal',123.33,212.123);" \
                               f"select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"
        text = '--step1:开启enable_query_parameterization参数;expect:开启成功--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization)
        logger.info(set_cmd1)

    def test_slowsql(self):
        text = '--step2:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step3:执行长度512的parameterized_query,查看query_parameterization_views;expect:有记录--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(f'INSERT 0 1', data_cmd, '执行失败' + text)
        self.assertIn(f'1 row', data_cmd, '执行失败' + text)
        self.assertIn(f'| INSERT     |', data_cmd, '执行失败' + text)

        text = '--step4:执行长度513的parameterized_query,查看query_parameterization_views;expect:没有记录--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(f'INSERT 0 1', data_cmd, '执行失败' + text)
        self.assertIn(f'0 rows', data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step5:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization_01)
        logger.info(set_cmd1)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
